﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace OestsDataBase
{
    public class ExamDAL
    {
        const string TABLE_NAME = "T_Exam";

        private static ExamModel ReadData(DataRow dr)
        {
            ExamModel em = new ExamModel
            {
                Id = Int32.Parse(dr["id"].ToString()),
                Name = dr["name"].ToString(),
                ExamType = (ExamType)Enum.Parse(typeof(ExamType), dr["type"].ToString()),
                ProjectId = Int32.Parse(dr["project_id"].ToString()),
                PaperId = Int32.Parse(dr["paper_id"].ToString()),
                BeginTime = Convert.ToDateTime(dr["begintime"].ToString()),
                EndTime = Convert.ToDateTime(dr["endtime"].ToString()),
                Time = Int32.Parse((dr["time"].ToString())),
                HandinTime = dr["handinTime"].Equals(DBNull.Value) ? 0 : Int32.Parse(dr["handinTime"].ToString()),
                PassGrade = Int32.Parse((dr["passGrade"].ToString())),
                IsOrder = (bool)dr["isOrder"],
                Describe = dr["describe"].ToString(),
                //Count = dr["count"].Equals( DBNull.Value)?0: int.Parse(dr["count"].ToString()),
                TeacherId = int.Parse(dr["teacherid"].ToString()),
                CheckAnswer = int.Parse(dr["checkanswer"].ToString())
            };
            #region 考试状态注释/弃用

            //ExamState state = (ExamState)Enum.Parse(typeof(ExamState), dr["state"].ToString());
            //string strSql = "";
            //if (state == ExamState.Draft)
            //    em.ExamState = state;
            //else if (state == ExamState.Processing)
            //{
            //    if (DateTime.Compare(em.EndTime, DateTime.Now) < 0)//比较时间，确定考试状态
            //    {
            //        em.ExamState = ExamState.Done;
            //        //更新数据库
            //        strSql = string.Format("update T_Exam set state='{0}' where id = @id", ExamState.Done.ToString());

            //    }
            //    else
            //        em.ExamState = ExamState.Processing;
            //}
            //else if (state == ExamState.Unprocess)
            //{
            //    if (DateTime.Compare(em.BeginTime, DateTime.Now) < 0)
            //    {
            //        em.ExamState = ExamState.Processing;
            //        strSql = string.Format("update T_Exam set state='{0}' where id = @id", ExamState.Processing.ToString());

            //    }
            //    else
            //        em.ExamState = ExamState.Unprocess;
            //}
            //else if (state == ExamState.Done)
            //    em.ExamState = ExamState.Done;
            //if (strSql != "")
            //    SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", Int32.Parse(dr["id"].ToString())));
            #endregion

            return em;
        }

        public static ExamModel GetExamByID(int examid)
        {
            ExamModel exam = new ExamModel();
            string sql_operate = "select * from T_Exam where id = " + examid;
            DataTable dt = SqlHelper.ExecuteDataTable(sql_operate);
            if (dt != null && dt.Rows.Count > 0)
            {
                exam = ReadData(dt.Rows[0]);
            }
            return exam;
        }
        /// <summary>
        /// 根据学员id和项目ID获取对应的操作考试列表
        /// </summary>
        /// <param name="stdID"></param>
        /// <param name="projectID"></param>
        /// <returns></returns>
        public static List<ExamModel> GetOperateExamsByStudent(int stdID, int projectID)
        {
            // string sql = string.Format("select T_Exam.* from T_StudentExamData" +
            //     " WHERE T_Exam.project_id={0} and T_StudentExamData.studentID={1} and T_Exam.id =T_StudentExamData.exam_id ", projectID, stdID);

            string sql = string.Format("select * from T_Exam Where T_Exam.id in(select exam_id from T_StudentExamData where studentID ={0}) " +
                "and T_Exam.type = '{1}' and T_Exam.project_id  = {2}", stdID, ExamType.Operate.ToString(), projectID);


            List<ExamModel> list = new List<ExamModel>();
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    ExamModel em = ReadData(dataRow);

                    if (DateTime.Compare(em.EndTime, DateTime.Now) >= 0 && DateTime.Compare(em.BeginTime, DateTime.Now) < 0)//比较时间，当前时间可考试
                    {
                        string strSql = string.Format("select count,grade from T_StudentExamData where studentID = {0} and exam_id = {1}", stdID, em.Id);
                        DataTable dt1 = SqlHelper.ExecuteDataTable(strSql);
                        if (dt1 != null && dt1.Rows.Count > 0)
                        {
                            em.Count = dt1.Rows[0]["count"].Equals(DBNull.Value) ? 0 : Int32.Parse(dt1.Rows[0]["count"].ToString());
                            em.StudentGrade = dt1.Rows[0]["grade"].Equals(DBNull.Value) ? 0 : Int32.Parse(dt1.Rows[0]["grade"].ToString());
                        }
                        list.Add(em);
                    }
                }
            }
            return list;


        }
        /// <summary>
        /// 根据学员id和项目ID获取对应的理论考试列表
        /// </summary>
        /// <param name="stdID"></param>
        /// <param name="projectID"></param>
        /// <returns></returns>
        public static List<ExamModel> GetTheoryExamsByStudent(int stdID, int projectID)
        {
            // string sql = string.Format("select T_Exam.* from T_StudentExamData" +
            //     " WHERE T_Exam.project_id={0} and T_StudentExamData.studentID={1} and T_Exam.id =T_StudentExamData.exam_id ", projectID, stdID);

            string sql = string.Format("select * from T_Exam Where T_Exam.id in(select exam_id from T_StudentExamData where studentID ={0}) " +
                "and T_Exam.type = '{1}' and T_Exam.project_id  = {2}", stdID, ExamType.Thoery.ToString(), projectID);


            List<ExamModel> list = new List<ExamModel>();
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    ExamModel em = ReadData(dataRow);

                    if (/*DateTime.Compare(em.EndTime, DateTime.Now) >= 0 &&*/ DateTime.Compare(em.BeginTime, DateTime.Now) < 0)//理论考试，只要考试开始时间小于当前时间即可考试
                    {
                        string strSql = string.Format("select count,grade from T_StudentExamData where studentID = {0} and exam_id = {1}", stdID, em.Id);
                        DataTable dt1 = SqlHelper.ExecuteDataTable(strSql);
                        if (dt1 != null && dt1.Rows.Count > 0)
                        {
                            em.Count = dt1.Rows[0]["count"].Equals(DBNull.Value) ? 0 : Int32.Parse(dt1.Rows[0]["count"].ToString());
                            em.StudentGrade = dt1.Rows[0]["grade"].Equals(DBNull.Value) ? 0 : Int32.Parse(dt1.Rows[0]["grade"].ToString());
                        }
                        list.Add(em);
                    }
                }
            }
            return list;


        }
        public static List<ExamModel> GetOperateExamsByProjectID(int projectID)
        {
            // List<Paper> papers = PaperDAL.GetPapers(projectID, PaperType.Operate);

            string sql_operate = string.Format("select * from T_Exam where type ='{0}' and project_id ={1}", ExamType.Operate.ToString(), projectID);
            List<ExamModel> list = new List<ExamModel>();
            DataTable dt = SqlHelper.ExecuteDataTable(sql_operate);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    list.Add(ReadData(dataRow));
                }
            }
            return list;
        }

        public static List<ExamModel> GetExamsByTeacher(int teacherId)
        {
            string sql_operate = "select * from T_Exam where teacherid = " + teacherId;
            List<ExamModel> list = new List<ExamModel>();
            DataTable dt = SqlHelper.ExecuteDataTable(sql_operate);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dt.Rows)
                {
                    ExamModel em = new ExamModel() { TeacherId = teacherId };
                    em.Id = Int32.Parse(dataRow["id"].ToString());
                    em.Name = dataRow["name"].ToString();
                    em.ExamType = (ExamType)Enum.Parse(typeof(ExamType), dataRow["type"].ToString());
                    em.ProjectId = Int32.Parse(dataRow["project_id"].ToString());
                    em.PaperId = Int32.Parse(dataRow["paper_id"].ToString());
                    em.BeginTime = Convert.ToDateTime(dataRow["begintime"].ToString());
                    em.EndTime = Convert.ToDateTime(dataRow["endtime"].ToString());
                    em.Time = Int32.Parse(dataRow["time"].ToString());
                    em.PassGrade = Int32.Parse(dataRow["passGrade"].ToString());
                    em.IsOrder = (bool)dataRow["isOrder"];
                    em.Describe = dataRow["describe"].ToString();
                    //  em.ExamState =(ExamState)Enum.Parse(typeof(ExamState), dataRow["state"].ToString());
                    //em.StudentList = GetStudentList(em.ExamType,em.Id); 
                    list.Add(em);
                }
            }
            return list;
        }

        /// <summary>
        /// 返回 Identity
        /// </summary>
        /// <param name="choice"></param>
        /// <returns></returns>
        public static int AddExam(ExamModel em)
        {
            // DBManager.Checkident(TABLE_NAME);
            string strSql = string.Format("insert into T_Exam (name,type,paper_id,begintime,endtime,time,passGrade,isOrder,describe,state,count,teacherid,project_id,handinTime,checkanswer) "
                + "values(@name,'{0}',{1},'{2}','{3}',{4},{5},{6},@describle,'{7}',{8},{9},{10},{11},{12});SELECT @@Identity",
              em.ExamType.ToString(), em.PaperId, em.BeginTime, em.EndTime, em.Time, em.PassGrade,
              em.IsOrder == false ? 0 : 1, em.ExamState.ToString(), em.Count, em.TeacherId, em.ProjectId, em.HandinTime, em.CheckAnswer);

            object obj = SqlHelper.ExecuteScalar(strSql, new SqlParameter("@name", em.Name), new SqlParameter("@describle", em.Describe ?? ""));
            return Int32.Parse(obj.ToString());
        }

        public static List<ExamModel> GetData()
        {
            List<ExamModel> list = new List<ExamModel>();
            string strSql = "select * from T_Exam";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                ExamModel scm = ReadData(dr);
                list.Add(scm);
            }
            return list;
        }


        public static bool Delete(int examid)
        {
            StudentExamDAL.DeleteExamInfoByExam(examid);

            string strSql = string.Format("delete from T_Exam where id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", examid));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool DelExamByTeacherID(int teacherid)
        {
            string strSql = string.Format("select * from T_Exam where teacherid = @teacherid");
            object n = SqlHelper.ExecuteScalar(strSql, new SqlParameter("@teacherid", teacherid));
            if (n != null)
            {
                ///删除考试卷中
                StudentExamDAL.DeleteExamInfoByTeacher(teacherid);
                string strSql1 = string.Format("delete from T_Exam where teacherid = @teacherid");
                SqlHelper.ExecuteScalar(strSql1, new SqlParameter("@teacherid", teacherid));
                return true;
            }
            return false;
        }

        public static bool DelExamByTeacherIDs(List<int> teacherids)
        {
            string idStr = string.Join(",", teacherids);

            //删除学生考试表
            string del_T_StudentExamData = $"delete from T_StudentExamData where T_StudentExamData.exam_id in(select id from T_Exam where teacherid in ({idStr}))";
            string del_T_Exam = $"delete from T_Exam where teacherid in ({idStr})";
            return SqlHelper.ExecuteTransaction(new List<string> { del_T_StudentExamData, del_T_Exam });

        }


        public static bool Modify(ExamModel em)
        {
            string strSql = string.Format("update T_Exam set name='{0}',type ='{1}',paper_id={2},begintime='{3}',endtime='{4}',time={5},passGrade={6},isOrder={7},describe='{8}',state='{9}',project_id ={10},handinTime={11},checkanswer={12} where id = @id",
                em.Name, em.ExamType, em.PaperId, em.BeginTime, em.EndTime, em.Time, em.PassGrade, em.IsOrder == false ? 0 : 1, em.Describe, em.ExamState.ToString(), em.ProjectId, em.HandinTime, em.CheckAnswer);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", em.Id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool CheckExist(string name)
        {
            string strSql = "select Count(*) from T_Exam where name = @name";
            object obj = SqlHelper.ExecuteScalar(strSql, new SqlParameter("@name", name));
            return ((int)obj > 0); ;
        }
    }
}
